<?php

namespace App\V3\V3_2_7\Models;

use App\V3\V3_2_7\Models\SellDriverModel;
use DateTimeInterface;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class SellModel extends Model
{


    use HasFactory;

    protected $table = 'sell';

    protected $dateFormat = 'U';//这个时间字段被为被格式化为UNIX 时间戳的形式存储
    //toArray 时间转换问题
    protected function serializeDate(DateTimeInterface $date)
    {
        return $date->format('Y-m-d H:i:s');
    }

    /**
     * $dates 数组内的列转换为日期格式
     *
     * @var array
     */
    protected $dates = [
        'sell_date',
        'deliver_time',
        'order_time',
        'collection_time',
        'debt_reminder_time'
    ];

    public function materielInfo()
    {
        return self::hasMany(SellGoodsDetailModel::class, 'sell_id', 'id');
    }

    public function packageInfo()
    {
        return self::hasMany(SellPackageDetailModel::class, 'sell_id', 'id');
    }

    public function sellLogisticsInfo()
    {
        return self::hasMany(SellLogisticsInfoModel::class, 'sell_id', 'id');
    }

    public function driverInfo()
    {
        return self::hasMany(SellDriverModel::class, 'sell_id', 'id');
    }

    static public function getSellInfo($userId)
    {
        return self::select('id')->where($userId)->from('sell as a')->get()->toArray();
    }

    static public function getSellIdInfo($userId, $data, $condition)
    {
        return self::select('id')
            ->from('sell as a')
            ->where($condition)
            ->where($userId)->get()->toArray();
    }

    static public function detail02($id, $mianId = '')
    {
        return self::select(
            'a.*',
            'b.username',
            'b.nickname'
            )
            ->from('sell as a')
            ->leftJoin('admin as b', 'b.id', '=', 'a.user_id')
            ->with('materielInfo', function($query) {
                $query->select(
                    'sell_goods_detail.id',
                    'sell_goods_detail.id as sell_goods_id',
                    'sell_goods_detail.materiel_type_id',
                    'a.materiel_name as name',
                    'a.type_name',
                    'a.materiel_no',
                    'a.materiel_img as image',
                    'price',
                    'sell_goods_detail.num',
                    'sell_goods_detail.out_num',
                    'b.storage_name as warehouse',
                    'b.id as warehouse_id',
                    'piece',
                    'sell_id',
                    'cost_price',
                    'sell_goods_detail.materiel_type_id',
                    'sell_goods_detail.shelf_id',
                    'sell_goods_detail.location_id',
                    's.shelf_name',
                    'l.location_name',
                    'a.is_use_serial',
                    'a.is_product_materiel'
                    )
                    ->with('content',function($query){
                        $query->select(
                            'materiel_product_content.id',
                            'a.materiel_name',
                            'a.type_name',
                            'a.materiel_img',
                            'a.materiel_no',
                            'product_id',
                            'materiel_product_content.num',
                            'materiel_product_content.price',
                            'materiel_product_content.total_price'
                            )->leftJoin('materiel_type as a', 'a.id', '=', 'materiel_product_content.type_id')
                            ->where('materiel_product_content.delete_flag',0)
                            ->where('a.delete_flag',0);
                    })
                    ->with('goodsBatchInfo', function($query) {
                        $query->select(
                            'sell_goods_detail_batch.id',
                            'storage_batch_id',
                            'batch_no',
                            'a.storage_manage_id',
                            'sell_goods_detail_id',
                            'sell_id',
                            'a.main_id',
                            'sell_goods_detail_batch.num',
                            'rest_num'
                            )
                            ->leftJoin('storage_batch as a', 'a.id', '=', 'sell_goods_detail_batch.storage_batch_id');
                    })
                    ->leftJoin('materiel_type as a', 'a.id', '=', 'sell_goods_detail.materiel_type_id')
                    ->leftJoin('storage as b', 'b.id', '=', 'sell_goods_detail.warehouse_id')
                    ->leftJoin('storage_shelf as s','sell_goods_detail.shelf_id','=','s.id')
                    ->leftJoin('storage_shelf_location as l','sell_goods_detail.location_id','=','l.id');
            })
            ->with('packageInfo', function($query) {
                $query->select(
                    'sell_package_detail.id',
                    'sell_package_detail.id as sell_package_id',
                    'package_id',
                    'package_name',
                    'price',
                    'total_cost',
                    'sell_id',
                    'num',
                    'out_num',
                    'package_no',
                    'piece'
                    )
                    ->leftJoin('package as p', 'p.id', '=', 'sell_package_detail.package_id')
                    ->with('packageContent', function($query) {
                        $query->select(
                            'sell_package_content.id',
                            'sell_package_content.id as sell_package_content_id',
                            'sell_package_content.materiel_class_id',
                            'sell_package_content.storage_id',
                            'sell_package_content.package_content_id',
                            'sell_package_content.storage_manage_id',
                            'sell_package_content.materiel_type_id',
                            'package_detail_id',
                            'mt.materiel_name',
                            'mt.type_name',
                            'mt.selling_price',
                            'mt.materiel_no',
                            'mt.materiel_img',
                            'mt.cost',
                            'mcs.class_name as s_class_name',
                            'mcs.class_no as s_class_no',
                            'mcf.class_name as f_class_name',
                            'mcf.class_no as f_class_no',
                            's.storage_name',
                            'sm.in_storage_num',
                            'sell_package_content.num',
                            'sell_package_content.total_num',
                            'sell_package_content.shelf_id',
                            'sell_package_content.location_id',
                            'se.shelf_name',
                            'lo.location_name',
                            'mt.is_use_serial'
                            )
                            ->with('packageBatchInfo', function($query) {
                                $query->select(
                                    'sell_package_batch.id',
                                    'sell_package_batch.sell_package_content_id',
                                    'sell_package_batch.storage_manage_id',
                                    'sell_package_batch.storage_batch_id',
                                    'sell_package_batch.sell_id',
                                    'sell_package_batch.main_id',
                                    'sell_package_batch.num',
                                    'sell_package_batch.rest_num',
                                    'batch_no',
                                    )
                                    ->leftJoin('storage_batch as a', 'a.id', '=', 'sell_package_batch.storage_batch_id');
                            })
                            ->leftJoin('materiel_type as mt', 'mt.id', '=', 'sell_package_content.materiel_type_id')
                            ->leftJoin('materiel_class as mcs', 'mcs.id', '=', 'sell_package_content.materiel_class_id')
                            ->leftJoin('materiel_class as mcf', 'mcf.id', '=', 'mcs.parent_id')
                            ->leftJoin('storage_manage as sm', 'sm.id', '=', 'sell_package_content.storage_manage_id')
                            ->leftJoin('storage as s', 's.id', '=', 'sell_package_content.storage_id')
                            ->leftJoin('storage_shelf as se','sell_package_content.shelf_id','=','se.id')
                            ->leftJoin('storage_shelf_location as lo','sell_package_content.location_id','=','lo.id');
                    });
            })
            ->with('driverInfo', function($query) {
                $query->select(
                    'id',
                    'plate_number',
                    'name',
                    'phone',
                    'logistics_company',
                    'remark',
                    'plate_image',
                    'id_card_image',
                    'driving_licence',
                    'sell_id'
                    );
            })
            ->where('a.id', $id)->first();
    }

    static public function detail($id, $mianId = '')
    {
        return self::select(
            'a.*',
            'b.username',
            'b.nickname'
        )
            ->from('sell as a')
            ->leftJoin('admin as b', 'b.id', '=', 'a.user_id')
            ->with('materielInfo', function($query) {
                $query->select(
                    'sell_goods_detail.id',
                    'sell_goods_detail.id as sell_goods_id',
                    'sell_goods_detail.materiel_type_id',
                    'a.materiel_name as name',
                    'a.type_name',
                    'a.materiel_no',
                    'a.materiel_img as image',
                    'sell_goods_detail.display_ratio',
                    'sell_goods_detail.calc_ratio',
                    'sell_goods_detail.is_product_materiel',
                    'price',
                    'sell_goods_detail.num',
                    'sell_goods_detail.out_num',
                    'b.storage_name as warehouse',
                    'b.id as warehouse_id',
                    'piece',
                    'sell_id',
                    'cost_price',
                    'sell_goods_detail.materiel_type_id',
                    'sell_goods_detail.shelf_id',
                    'sell_goods_detail.location_id',
                    's.shelf_name',
                    'l.location_name',
                    'a.is_use_serial'
                    )->with('goodsBatchInfo', function($query) {
                        $query->select(
                            'sell_goods_detail_batch.id',
                            'storage_batch_id',
                            'batch_no',
                            'a.storage_manage_id',
                            'sell_goods_detail_id',
                            'sell_id',
                            'a.main_id',
                            'sell_goods_detail_batch.num',
                            'rest_num'
                        )
                        ->leftJoin('storage_batch as a', 'a.id', '=', 'sell_goods_detail_batch.storage_batch_id');
                    })
                    ->leftJoin('materiel_type as a', 'a.id', '=', 'sell_goods_detail.materiel_type_id')
                    ->leftJoin('storage as b', 'b.id', '=', 'sell_goods_detail.warehouse_id')
                    ->leftJoin('storage_shelf as s','sell_goods_detail.shelf_id','=','s.id')
                    ->leftJoin('storage_shelf_location as l','sell_goods_detail.location_id','=','l.id')
                    ->whereRaw('num > out_num');
            })
            ->with('packageInfo', function($query) {
                $query->select(
                    'sell_package_detail.id',
                    'sell_package_detail.id as sell_package_id',
                    'package_id',
                    'package_name',
                    'price',
                    'total_cost',
                    'sell_id',
                    'num',
                    'out_num',
                    'package_no',
                    'piece'
                )
                    ->leftJoin('package as p', 'p.id', '=', 'sell_package_detail.package_id')
                    ->with('packageContent', function($query) {
                        $query->select(
                            'sell_package_content.id',
                            'sell_package_content.id as sell_package_content_id',
                            'sell_package_content.materiel_class_id',
                            'sell_package_content.storage_id',
                            'sell_package_content.package_content_id',
                            'sell_package_content.storage_manage_id',
                            'sell_package_content.materiel_type_id',
                            'package_detail_id',
                            'mt.materiel_name',
                            'mt.type_name',
                            'mt.selling_price',
                            'mt.materiel_no',
                            'mt.materiel_img',
                            'mt.cost',
                            'mcs.class_name as s_class_name',
                            'mcs.class_no as s_class_no',
                            'mcf.class_name as f_class_name',
                            'mcf.class_no as f_class_no',
                            's.storage_name',
                            'sm.in_storage_num',
                            'sell_package_content.num',
                            'sell_package_content.total_num',
                            'sell_package_content.shelf_id',
                            'sell_package_content.location_id',
                            'se.shelf_name',
                            'lo.location_name',
                            'mt.is_use_serial'
                        )
                            ->with('packageBatchInfo', function($query) {
                                $query->select(
                                    'sell_package_batch.id',
                                    'sell_package_batch.sell_package_content_id',
                                    'sell_package_batch.storage_manage_id',
                                    'sell_package_batch.storage_batch_id',
                                    'sell_package_batch.sell_id',
                                    'sell_package_batch.main_id',
                                    'sell_package_batch.num',
                                    'sell_package_batch.rest_num',
                                    'batch_no',
                                )
                                    ->leftJoin('storage_batch as a', 'a.id', '=', 'sell_package_batch.storage_batch_id');
                            })
                            ->leftJoin('materiel_type as mt', 'mt.id', '=', 'sell_package_content.materiel_type_id')
                            ->leftJoin('materiel_class as mcs', 'mcs.id', '=', 'sell_package_content.materiel_class_id')
                            ->leftJoin('materiel_class as mcf', 'mcf.id', '=', 'mcs.parent_id')
                            ->leftJoin('storage_manage as sm', 'sm.id', '=', 'sell_package_content.storage_manage_id')
                            ->leftJoin('storage as s', 's.id', '=', 'sell_package_content.storage_id')
                            ->leftJoin('storage_shelf as se','sell_package_content.shelf_id','=','se.id')
                            ->leftJoin('storage_shelf_location as lo','sell_package_content.location_id','=','lo.id');
                    });
            })
            ->with('driverInfo', function($query) {
                $query->select(
                    'id',
                    'plate_number',
                    'name',
                    'phone',
                    'logistics_company',
                    'remark',
                    'plate_image',
                    'id_card_image',
                    'driving_licence',
                    'sell_id',
                );
            })
            ->where('a.id', $id)->first();
    }

    public static function getSellMainInfo($id)
    {
        $rs = self::select(
            'a.*',
            'b.username',
            'b.nickname',
            'c.username as approver_name'
        )
            ->from('sell as a')
            ->leftJoin('admin as b', 'b.id', '=', 'a.user_id')
            ->leftJoin('admin as c', 'c.id', '=', 'a.approver')
            ->with('driverInfo', function($query) {
                $query->select(
                    'id',
                    'plate_number',
                    'name',
                    'phone',
                    'logistics_company',
                    'remark',
                    'plate_image',
                    'id_card_image',
                    'driving_licence',
                    'sell_id'
                )->get();
            })->with('sellLogisticsInfo', function($query) {
                $query->select(
                    'id',
                    'sell_id',
                    'logistics_company',
                    'logistics_order_no'
                    )/*->where('delete_flag',0)*/;
            })
            ->where([
                'a.id' => $id,
                'a.delete_flag' => 0
            ])->first();

        if(!empty($rs)){
            return $rs->toArray();
        }else{
            return [];
        }

    }
    //今日销售金额
    static public function getTodaySellMoney($condition, $startTime, $endTime){

        $info = SellModel::select(
            DB::raw('SUM(final_money-real_money) as total_debt')
        )
            ->where(['repeal_status' => 0])
            ->where($condition)
            ->where(function($query) {
                $query->orWhere('out_status', '!=', 0);
                $query->orWhere('pay_status', '!=', 0);
            })
            ->whereBetween('created_at', [$startTime, $endTime])
            ->first();

        return empty($info['total_debt'])?0:sprintf('%.6f', $info['total_debt']);
    }

    static public function getTodaySellCount($condition, $startTime, $endTime){
        return SellModel::where(['repeal_status' => 0])
            ->where($condition)
            ->whereBetween('created_at', [$startTime, $endTime])
            ->count();
    }

    /**
     * 快速出入库同步价格
     * @param Request $data
     */
    static public function getSyncPrice($data)
    {
        $idArr = explode(',', $data['id']);
        $sellData = [];
        if(count($idArr) > 0){
            foreach ($idArr as $key => $gid){
                $sellInfo = SELF::query()->select(
                'price',
                'b.materiel_type_id as id',
                'c.cost as cost_price'
                )->from('sell as a')
                ->leftJoin(
                'sell_goods_detail as b',
                'b.sell_id','=','a.id'
                ) ->leftJoin(
                        'materiel_type as c',
                        'b.materiel_type_id','=','c.id'
                )->where([
                'a.delete_flag' => 0,
                'b.repeal_status' => 0,
                'a.main_id' => $data['main_id']
                ])->where('materiel_type_id',$gid);
                if(!empty($data['customer_id'])){
                    $sellInfo->where('customer_id',$data['customer_id']);
                }
                $sellInfo = $sellInfo->orderBy('a.id','desc')->first();
                $sellData[$key] = [
                    'price' => '',
                    'id' => $gid,
                    'cost_price' => ''
                ];

                if(!empty($sellInfo)){
                    $sellData[$key] = $sellInfo->toArray();
                }
            }
        }

        return $sellData;
    }

    public static function getbarterSellInfo($id)
    {
        $rs = self::select(
            'a.id',
            'a.customer_id',
            'a.customer_name',
            'a.approver as approver_id',
            'a.consignee',
            'a.phone',
            'a.address',
            'a.proof_image',
            'a.final_money',
            'a.money',
            'a.real_money',
            'a.other_charges',
            'a.remark',
            'a.out_order_no',
            'a.out_status',
            'a.pay_status',
            'a.deliver_person',
            'a.deliver_phone',
            'a.deliver_addr',
            'a.deliver_goods_type',
            'a.transport_type',
            'a.source',
            'c.nickname as approver_name'
            )
            ->from('sell as a')
            ->leftJoin('admin as b', 'b.id', '=', 'a.user_id')
            ->leftJoin('admin as c', 'b.id', '=', 'a.approver')
            ->with('driverInfo', function($query) {
                $query->select(
                    'id',
                    'plate_number',
                    'name',
                    'phone',
                    'logistics_company',
                    'remark',
                    'plate_image',
                    'id_card_image',
                    'driving_licence',
                    'sell_id'
                    );
            })->with('sellLogisticsInfo', function($query) {
                $query->select(
                    'sell_id',
                    'logistics_company',
                    'logistics_order_no'
                    );
            })
            ->where([
                'a.id' => $id,
                'a.delete_flag' => 0
            ])->first();

        if(!empty($rs)){
            return $rs->toArray();
        }else{
            return [];
        }
    }
}
